package com.handy.plugin.auth.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.handy.database.DBUtil;
import com.handy.plugin.auth.bean.HAuthRole;
import com.handy.plugin.auth.bean.HAuthUser;

public class HAuthUserDAO {
	
	private DBUtil dbUtil = DBUtil.getInstance();

	/**
	 * 添加操作.
	 * 
	 * @param user
	 */
	public void save(HAuthUser user) {
		// 组装sql语句
		StringBuffer sb = new StringBuffer();
		sb.append("insert into ").append(TABLENAME).append(" ( ");
		sb.append(ID).append(",");
		sb.append(USERNAME).append(",");
		sb.append(PASSWORD).append(",");
		sb.append(REALNAME).append(",");
		sb.append(GENDER).append(",");
		sb.append(PHONE).append(",");
		sb.append(EMAIL).append(",");
		sb.append(CREATETIME).append(",");
		if (null!=user.getCendtime() && !"".equals(user.getCendtime())){
			sb.append(ENDTIME).append(",");
		}
		sb.append(STATE);
		sb.append(") values ( ");
		sb.append(TABLESEQ).append(".nextval, ");// oracle
		sb.append("'").append(user.getUsername()).append("',");
		sb.append("'").append(user.getPassword()).append("',");
		sb.append("'").append(user.getRealname()).append("',");
		sb.append("'").append(user.getGender()).append("',");
		sb.append("'").append(user.getPhone()).append("',");
		sb.append("'").append(user.getEmail()).append("',");
		sb.append("sysdate,");
		if (null!=user.getCendtime() && !"".equals(user.getCendtime())){
			sb.append("to_date('").append(user.getCendtime()).append("',yyyy-mm-dd hh24:mi:ss'");
		}
		sb.append("'").append(user.getState()).append("'");
		sb.append(")");

		if (log.isDebugEnabled()) {
			log.info("saving " + TABLENAME + " instance");
			log.info(sb.toString());
		}

		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		try {
			run.update(sb.toString());
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
	}

	/**
	 * 删除表中id号为userId的记录。
	 * 
	 * @param userId
	 */
	public void delete(Integer userId) {

		StringBuffer sb = new StringBuffer();
		sb.append("delete from ").append(TABLENAME);
		sb.append(" where ").append(ID).append(" = ");
		sb.append(userId);

		if (log.isDebugEnabled()) {
			log.info("deleting " + TABLENAME + " instance");
			log.info(sb.toString());
		}

		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		try {
			run.update(sb.toString());
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
	}

	/**
	 * 删除表中指定roleid的记录。
	 * 
	 * @param roleid
	 */
	public void deleteByRoleid(Integer roleid) {
		StringBuffer sb = new StringBuffer();
		sb.append("delete from ").append(TABLENAME);
		sb.append(" where ").append(ROLEID).append(" = ");
		sb.append(roleid);

		if (log.isDebugEnabled()) {
			log.info("deleting " + TABLENAME + " instance by roleid:" + roleid);
			log.info(sb.toString());
		}

		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		try {
			run.update(sb.toString());
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
	}

	/**
	 * 修改指定的XcmsUser
	 * 
	 * @param user
	 */
	public void update(HAuthUser user) {
		StringBuffer sb = new StringBuffer();
		sb.append("update ").append(TABLENAME).append(" set ");
		//sb.append(USERNAME).append(" = ").append("'").append(user.getUsername()).append("',");
		sb.append(REALNAME).append(" = ").append("'")
				.append(user.getRealname()).append("',");
//		sb.append(ROLEID).append(" = ").append("'").append(user.getRoleid())
//				.append("',");
		sb.append(GENDER).append(" = ").append("'").append(user.getGender())
				.append("',");
		sb.append(PHONE).append(" = ").append("'").append(user.getPhone())
				.append("',");
		sb.append(EMAIL).append(" = ").append("'").append(user.getEmail())
				.append("',");
		sb.append(STATE).append(" = ").append("'").append(user.getState())
				.append("'");
		if (null!=user.getCendtime() && !"".equals(user.getCendtime())){
			sb.append(ENDTIME).append("=").append("to_date('").append(user.getCendtime()).append("',yyyy-mm-dd hh24:mi:ss'");
		}
		sb.append(" where ").append(ID).append(" = ");
		sb.append(user.getId());
		log.info(sb.toString());
		if (log.isDebugEnabled()) {
			log.info("update " + TABLENAME + " instance");
			log.info(sb.toString());
		}

		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		try {
			run.update(sb.toString());
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
	}

	/**
	 * 修改某个用户的口令。
	 * 
	 * @param userId
	 *            用户编号
	 * @param password
	 *            口令
	 */
	public void updatePassword(Integer userId, String password) {
		StringBuffer sb = new StringBuffer();
		sb.append("update ").append(TABLENAME).append(" ");
		sb.append("set ").append(PASSWORD).append(" = ").append("'").append(
				password).append("'");
		sb.append(" where ").append(ID).append(" = ");
		sb.append(userId);

		if (log.isDebugEnabled()) {
			log.info("update " + TABLENAME + " instance");
			log.info(sb.toString());
		}

		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		try {
			run.update(sb.toString());
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
	}

	/**
	 * 修改某个用户的状态。
	 * 
	 * @param userId
	 * @param state
	 */
	public void changeState(Integer userId, Integer state) {
		StringBuffer sb = new StringBuffer();
		sb.append("update ").append(TABLENAME).append(" ");
		sb.append("set ").append(STATE).append(" = ").append("'").append(state)
				.append("'");
		sb.append(" where ").append(ID).append(" = ");
		sb.append(userId);

		if (log.isDebugEnabled()) {
			log.info("update " + TABLENAME + " instance");
			log.info(sb.toString());
		}

		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		try {
			run.update(sb.toString());
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
	}

	/**
	 * 查找指定id的记录。
	 * 
	 * @param id
	 * @return
	 */
	public HAuthUser findById(Integer userId) {
		StringBuffer sb = new StringBuffer();
		sb.append("select * from ").append(TABLENAME);
		sb.append(" where ").append(ID).append(" = ");
		sb.append(userId);

		if (log.isDebugEnabled()) {
			log.info("getting " + TABLENAME + " instance with id: " + userId);
			log.info(sb.toString());
		}

		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		BeanHandler h = new BeanHandler(HAuthUser.class);
		HAuthUser user = null;
		try {
			user = (HAuthUser) run.query(sb.toString(), h);
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
		return user;
	}

	@SuppressWarnings("unchecked")
	public List<HAuthRole> findRolesByUserId(Integer userId) {
		List<HAuthRole> list = null;
		StringBuffer sb = new StringBuffer();
		sb.append("select * from H_AUTH_ROLES");
		sb.append(" where ").append(ID).append(" in( select roleid from H_AUTH_ROLE_USERS where userid =  ");
		sb.append(userId).append(")");

		if (log.isDebugEnabled()) {
			log.info("getting " + TABLENAME + " instance with id: " + userId);
			log.info(sb.toString());
		}

		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		BeanListHandler h = new BeanListHandler(HAuthRole.class);
		try {
			list = (List<HAuthRole>) run.query(sb.toString(), h);
			
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
		return list;
	}
	
	
	/**
	 * 查找指定username的记录。
	 * 
	 * @param id
	 * @return
	 */
	public HAuthUser findByUsername(String username) {
		StringBuffer sb = new StringBuffer();
		sb.append("select * from ").append(TABLENAME);
		sb.append(" where ").append(USERNAME).append(" = ");
		sb.append("'").append(username).append("'");

		if (log.isDebugEnabled()) {
			log.info("getting " + TABLENAME + " instance with username: "
					+ username);
			log.info(sb.toString());
		}
		
		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		BeanHandler h = new BeanHandler(HAuthUser.class);
		HAuthUser user = null;
		try {
			user = (HAuthUser) run.query(sb.toString(), h);
		} catch (SQLException e) {
			log.error(e.getMessage());
		}

		if (user != null) {
			List<HAuthRole> role = findRolesByUserId(user.getId());
			user.setRoles(role);
		}
		return user;
	}

	@SuppressWarnings("unchecked")
	public List<HAuthUser> findByRoleid(Integer id) {
		List list = null;
		StringBuffer sb = new StringBuffer();
		sb.append("select * from ").append(TABLENAME);
		sb.append(" where ").append(ROLEID).append(" = ");
		sb.append(id);

		if (log.isDebugEnabled()) {
			log.info("getting " + TABLENAME + " instance with roleid: " + id);
			log.info(sb.toString());
		}

		BeanListHandler h = new BeanListHandler(HAuthUser.class);
		QueryRunner run = new QueryRunner(dbUtil.dataSource(getDataSourceName()));
		try {
			list = (List<HAuthUser>) run.query(sb.toString(), h);
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
		return list;
	}

	private static final Log log = LogFactory.getLog(HAuthUserDAO.class);

	private String dataSourceName;

	public String getDataSourceName() {
		return dataSourceName;
	}

	public void setDataSourceName(String dataSourceName) {
		this.dataSourceName = dataSourceName;
	}

	private static final String TABLENAME = "h_auth_users";
	private static final String TABLESEQ = "h_auth_user_seq";
	private static final String ID = "id";
	private static final String USERNAME = "username";
	private static final String PASSWORD = "password";
	private static final String REALNAME = "realname";
	private static final String ROLEID = "roleid";
	private static final String GENDER = "gender";
	private static final String PHONE = "phone";
	private static final String EMAIL = "email";
	private static final String CREATETIME="createtime";
	private static final String ENDTIME="endtime";
	private static final String STATE = "state";
}